# 发送消息
import json
import time
from flask import request, jsonify

from config_loader import load_config
import pymysql
from datetime import time
import requests
from datetime import datetime, timedelta
import re

# 加载配置
cfg = load_config("config.yml")
QX_API_URL = cfg["QX_API_URL"]
TIP_EXAMPLE = cfg["TIP_EXAMPLE"]
PREFIX_STR = cfg["PREFIX_STR"]

# 数据库配置
host = cfg["MYSQL"]["HOST"]
port = cfg["MYSQL"]["PORT"]
database = cfg["MYSQL"]["DATABASE"]
charset = cfg["MYSQL"]["CHARSET"]
user = cfg["MYSQL"]["USER"]
password = cfg["MYSQL"]["PASSWORD"]


# 公共参数
weekday_dict = {'Monday': '一', 'Tuesday': '二', 'Wednesday': '三', 'Thursday': '四', 'Friday': '五', 'Saturday': '六',
                'Sunday': '日'}


# DB创建
def mysql_db():
    conn = pymysql.connect(
        host=host,
        port=port,
        database=database,
        charset=charset,
        user=user,
        password=password
    )
    return conn


# 发送文本消息【千寻】
def send_text_msg_qx(robot_wxid, to_wxid, msg):
    """
    发送文字消息(好友或者群)
    * @access public
    * @param  string wxid 登录账号id，群和个人都是这个
    * @param  string msg 消息内容
    """
    data = {
        "type": "Q0001",
        "data": {
            "wxid": to_wxid,
            "msg": msg
        }
    }
    headers = {'Content-Type': 'application/json'}
    time.sleep(2)  # 服务端延迟推送
    response = requests.post(QX_API_URL, params={"wxid": robot_wxid}, headers=headers, data=json.dumps(data))
    if response.status_code != 200:
        print("Failed to send message:", response.content)


# 发送文本消息【微信公众号】
def make_text_msg_club(from_user, to_user, msg):
    """
    回复文本消息
    * @access public
    * @param  string to_user 接收者微信ID
    * @param  string msg 消息内容
    """
    reply = """
    <xml>
    <ToUserName><![CDATA[{from_user}]]></ToUserName>
    <FromUserName><![CDATA[{to_user}]]></FromUserName>
    <CreateTime>{time}</CreateTime>
    <MsgType><![CDATA[text]]></MsgType>
    <Content><![CDATA[{msg}]]></Content>
    </xml>
    """
    return reply.format(from_user=from_user, to_user=to_user, time=int(time.time()), msg=msg)


# server酱消息推送
def send_to_server_chan(sckey, text, desp):
    url = f"https://sc.ftqq.com/{sckey}.send"
    data = {
        "text": text,
        "desp": desp
    }
    response = requests.post(url, data=data)
    if response.status_code != 200:
        print("Failed to send message:", response.content)


# 设闹钟设置处理
# 示例：10月27日至11月27日 打卡 每天 8:00 建行CC豆 建行CC豆 www.baidu.com
def set_alarm(conn, alarm_string):
    # 按空格拆分字符串
    parts = alarm_string.split()
    # 获取各部分
    date_range = parts[0]
    alarm_kind = parts[1]  # 闹钟类型: 打卡|抢购
    alarm_frequency = parts[2]  # 闹钟频率
    alarm_time = parts[3]  # 闹钟时间
    alarm_title = parts[4]
    alarm_detail = parts[5]
    alarm_link = parts[6]

    # 解析开始时间和结束时间
    start_date, end_date = date_range.split('至')
    # 对具体时间进行处理，分割小时和分钟

    hour, minute = map(int, alarm_time.split(':'))
    # 转换为一天中的秒数
    final_time = hour * 3600 + minute * 60

    # 获取当前的日期和时间
    now = datetime.now()
    # 将datetime对象转换为字符串
    alarm_time_str = now.strftime('%Y-%m-%d %H:%M:%S')

    # 保存到数据库
    sql = ''' INSERT INTO alarms(start_date, end_date, alarm_time, frequency, alarm_title, alarm_detail, alarm_link, alarm_kind, alarmed_time)
              VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s) '''
    alarm = (start_date, end_date, final_time, alarm_frequency, alarm_title, alarm_detail, alarm_link, alarm_kind, 0)
    cur = conn.cursor()
    try:
        cur.execute(sql, alarm)
        conn.commit()
    except Exception as e:
        print('闹钟设置失败，err=', str(e))
        return '闹钟设置失败，错误信息：' + str(e)
    return '【设置成功】'


def get_alarms_from_db(conn):
    # 获取当前时间和5分钟后的时间
    now = datetime.now()
    future = now + timedelta(minutes=5)
    # 将当前时间和5分钟后的时间转换为当天的秒数
    now_seconds = (now - now.replace(hour=0, minute=0, second=0, microsecond=0)).total_seconds()
    future_seconds = (future - future.replace(hour=0, minute=0, second=0, microsecond=0)).total_seconds()
    # 从数据库中获取那些在接下来的5分钟内需要提醒的闹钟
    cur = conn.cursor()
    cur.execute(
        "SELECT * FROM alarms WHERE alarm_time >= %s AND alarm_time <= %s AND start_date <= %s AND end_date >= %s AND alarm_kind = 0",
        (now_seconds, future_seconds, now.strftime('%m月%d日'), now.strftime('%m月%d日')))
    alarms = cur.fetchall()

    return alarms


# 从数据库获取今日需要打卡的打卡事件
def get_sign_in_alarms(conn):
    cur = conn.cursor()
    cur.execute("SELECT * FROM alarms WHERE alarm_kind = '打卡'")
    alarms = cur.fetchall()

    return alarms


def db_get_alarms_list(conn, alarm_kind):
    action = ''
    if alarm_kind == 1:
        action = '打卡'
    elif alarm_kind == 2:
        action = '抢购'
    elif alarm_kind == 3:
        action = '地铁'
    cur = conn.cursor()
    cur.execute(f"SELECT * FROM alarms WHERE alarm_kind = '{action}'")
    alarms = cur.fetchall()

    return alarms


def get_all_alarms(conn):
    cur = conn.cursor()
    cur.execute("SELECT * FROM alarms")
    alarms = cur.fetchall()

    return alarms


# 处理设置闹钟
def handle_set_alarm_qx(wxid, fromWxid, msg):
    # 当收到的消息是时间时，设置定时任务,会先检查消息格式
    pattern = r'\d{1,2}月\d{1,2}日至\d{1,2}月\d{1,2}日 .+'
    if not re.match(pattern, msg):
        send_text_msg_qx(wxid, fromWxid, '输入有误，请按照示例格式输入！')
    else:
        conn = mysql_db()
        # 设置闹钟
        response = set_alarm(conn, msg)
        send_text_msg_qx(wxid, fromWxid, response)


def handle_set_alarm_club(from_user, to_user, msg):
    # 当收到的消息是时间时，设置定时任务,会先检查消息格式
    pattern = r'\d{1,2}月\d{1,2}日至\d{1,2}月\d{1,2}日 .+'
    reply_to_weixin = ''
    if not re.match(pattern, msg):
        reply_to_weixin = make_text_msg_club(from_user, to_user, '输入格式有误，请按照示例格式输入！')
    else:
        conn = mysql_db()
        # 设置闹钟
        response = set_alarm(conn, msg)
        reply_to_weixin = make_text_msg_club(from_user, to_user, response)

    return reply_to_weixin


# 处理签到打卡数据
def handle_alarms_query(wxid, fromWxid, user_state):
    # 获取今天的日期和星期
    today = datetime.now()
    weekday = '每周' + weekday_dict[today.strftime('%A')]
    date = '每月' + today.strftime('%d') + '号'
    # 连接到数据库
    conn = mysql_db()
    cursor = conn.cursor()
    # 获取当前日期
    current_date = datetime.now().strftime('%m月%d日')
    # 执行查询
    cursor.execute(
        "SELECT id, start_date, end_date, alarm_time, frequency, alarm_title, alarm_detail, alarm_link, alarm_kind "
        "FROM alarms WHERE start_date <= %s AND end_date >= %s",
        (current_date, current_date))
    # 获取查询结果
    results = cursor.fetchall()
    # 如果没有闹钟事件
    if not results:
        send_text_msg_qx(wxid, fromWxid, '今天没有闹钟事件。')
    else:
        # 否则，收集所有的闹钟事件
        alarms = []
        # 在开头添加一个大标题
        alarms.append(
            '😘😘😘查询成功😘😘😘' + chr(13) + PREFIX_STR + chr(13) + '只显示当天数据' + chr(13) + PREFIX_STR + chr(13))
        # 否则，回复所有的闹钟事件
        for row in results:
            frequency = row[4]
            if frequency == '每天' or frequency == weekday or frequency == date:
                # 将每个闹钟的信息分别添加到列表中
                alarm_info = f"【{row[8]}】{row[5]}" + chr(13) + f"【内容】{row[6]}" + chr(13) + f"【直达】{row[7]}" + chr(
                    13) + PREFIX_STR + chr(13)
                alarms.append(alarm_info)
        if len(alarms) <= 0:
            send_text_msg_qx(wxid, fromWxid, '今天没有闹钟事件。')
        else:
            # 使用join()函数，将列表中的每个元素连接成一个字符串，每个元素之间用换行符分隔
            response = ''.join(alarms)
            # 发送结果给用户
            send_text_msg_qx(wxid, fromWxid, response)

    # 更新用户状态
    user_state[fromWxid] = 'waiting_for_id'


# 处理签到打卡数据
def handle_alarm_sign_in_query(wxid, fromWxid, user_state):
    # 获取今天的日期和星期
    today = datetime.now()
    weekday = '每周' + weekday_dict[today.strftime('%A')]
    date = '每月' + today.strftime('%d') + '号'
    # 连接到数据库
    conn = mysql_db()
    cursor = conn.cursor()
    # 获取当前日期
    current_date = datetime.now().strftime('%m月%d日')

    # 执行查询
    # cursor.execute("SELECT id, start_date, end_date, alarm_time, frequency, alarm_title, alarm_detail, "
    #                "alarm_link, alarm_kind FROM alarms WHERE start_date <= %s AND end_date >= %s and alarm_kind='打卡'",
    #                (current_date, current_date))
    cursor.execute("SELECT id, start_date, end_date, alarm_time, frequency, alarm_title, alarm_detail, "
                   "alarm_link, alarm_kind FROM alarms WHERE alarm_kind='打卡'")
    # 获取查询结果
    results = cursor.fetchall()
    # 如果没有闹钟事件
    if not results:
        send_text_msg_qx(wxid, fromWxid, '今天没有待处理的东东。')
    else:
        # 否则，收集所有的闹钟事件
        alarms = []
        # 在开头添加一个大标题
        alarms.append('😄😄😄今日打卡😄😄😄' + chr(13) + PREFIX_STR + chr(13))
        # 否则，回复所有的闹钟事件
        for row in results:
            frequency = row[4]
            if frequency == '每天' or frequency == weekday or frequency == date:
                # 将每个闹钟的信息分别添加到列表中
                alarm_info = f"【{row[8]}】{row[5]}" + chr(13) + f"【内容】{row[6]}" + chr(13) + f"【直达】{row[7]}" + chr(
                    13) + PREFIX_STR + chr(13)
                alarms.append(alarm_info)
        if len(alarms) <= 0:
            send_text_msg_qx(wxid, fromWxid, '今天没有要处理的东西！')
        else:
            # 使用join()函数，将列表中的每个元素连接成一个字符串，每个元素之间用换行符分隔
            response = ''.join(alarms)
            # 发送结果给用户
            send_text_msg_qx(wxid, fromWxid, response)

    # 更新用户状态
    user_state[fromWxid] = 'normal'


# 处理具体闹钟数据查询
def handle_alarm_info(wxid, fromWxid, msg, user_state):
    # 连接到数据库
    conn = mysql_db()
    cursor = conn.cursor()
    # 执行查询
    cursor.execute("SELECT id, start_date, end_date, alarm_time, alarm_title, alarm_detail FROM alarms WHERE id = %s",
                   (msg,))
    # 获取查询结果
    result = cursor.fetchone()
    if result is None:
        # 如果没有找到指定的ID，发送一条消息给用户
        send_text_msg_qx(wxid, fromWxid, '没有找到指定的ID')
    else:
        # 格式化结果
        id, start_date, end_date, time, title, content = result
        # 将秒数转换为时间格式
        timeStr = datetime.fromtimestamp(time).strftime('%H:%M')
        # 创建一个空的列表来存储闹钟的信息
        alarm_info = []
        # 将每个字段的信息分别添加到列表中
        alarm_info.append(f"😄{title}😄")
        alarm_info.append(f"【时间区间】{start_date}-{end_date}")
        alarm_info.append(f"【提醒日期】{timeStr}")
        alarm_info.append(f"【内容】")
        alarm_info.append(content)
        # 使用join()函数，将列表中的每个元素连接成一个字符串，每个元素之间用换行符分隔
        response = '\n'.join(alarm_info)
        # 发送结果给用户
        send_text_msg_qx(wxid, fromWxid, response)
    # 更新用户状态
    user_state[fromWxid] = 'normal'


# 更新打卡状态
def update_alarm_status(wxid, fromWxid, msg, user_state):
    # 连接到数据库
    conn = mysql_db()
    cursor = conn.cursor()
    # 执行查询
    cursor.execute("UPDATE alarms SET alarmed_time = 1 WHERE id = %s", (msg,))
    # 提交事务
    conn.commit()
    # 检查是否删除成功
    if cursor.rowcount == 0:
        send_text_msg_qx(wxid, fromWxid, '更新打卡状态失败')
    else:
        send_text_msg_qx(wxid, fromWxid, 'OK OK')
    # 更新用户状态
    # user_state[fromWxid] = 'normal'


# 处理具体闹钟数据查询
def handle_alarm_delete(wxid, fromWxid, msg, user_state):
    # 连接到数据库
    conn = mysql_db()
    cursor = conn.cursor()
    # 执行删除操作
    cursor.execute("DELETE FROM alarms WHERE id = %s", (msg,))
    # 提交事务
    conn.commit()
    # 检查是否删除成功
    if cursor.rowcount == 0:
        send_text_msg_qx(wxid, fromWxid, '删除失败，没有找到指定的ID')
    else:
        send_text_msg_qx(wxid, fromWxid, '删除成功')
    # 更新用户状态
    user_state[fromWxid] = 'normal'


# 打卡、抢购列表
# def query_alarms_list():
#     # 获取今天的日期和星期
#     today = datetime.now()
#     weekday = '每周' + weekday_dict[today.strftime('%A')]
#     date_str = '每月' + today.strftime('%d') + '号'
#     # 获取当前时间
#     now = datetime.now()
#     conn = mysql_db()
#     alarms = db_get_alarms_list(conn, "抢购")
#     alarms_list = []
#     for alarm in alarms:
#         start_date_obj = datetime.strptime(alarm[1], '%Y-%m-%d').date()
#         end_date_obj = datetime.strptime(alarm[2], '%Y-%m-%d').date()
#         # 判断当前日期是否在开始日期和结束日期范围内
#         if start_date_obj <= now.date() <= end_date_obj:
#             if alarm[4] == '每天' or alarm[4] == weekday or alarm[4] == date_str:
#                 # 将每个闹钟的 ID 添加到列表中
#                 alarms_list.append({
#                     "id": alarm[0],
#                     "start_date": alarm[1],
#                     "end_date": alarm[2],
#                     "alarm_time": alarm[3],
#                     "frequency": alarm[4],
#                     "alarm_title": alarm[5],
#                     "alarm_detail": alarm[6],
#                     "alarm_link": alarm[7],
#                     "alarm_kind": alarm[8],
#                     "is_done_today": alarm[9],
#                     "week_already_count": alarm[10],
#                     "month_already_count": alarm[11],
#                     "week_count": alarm[12],
#                     "month_count": alarm[13]
#                 })
#     # 将alarms_list转换为字典
#     alarms_dict = {"list": alarms_list}
#     # 返回JSON数据
#     return alarms_dict


# 判断数据库updated_at字段是不是今天
def is_today(updated_at):
    # 获取 updated_at 的日期（年、月、日）
    updated_at_date = updated_at.date()

    # 获取今天的日期
    today_date = datetime.today().date()

    # 返回 updated_at 是否是今天
    return updated_at_date == today_date


# 今日打卡列表
def query_alarms_list(alarms_kind):
    # 获取今天的日期和星期
    today = datetime.now()
    weekday = '每周' + weekday_dict[today.strftime('%A')]
    date_str = '每月' + today.strftime('%d') + '号'
    # 获取当前时间
    now = datetime.now()
    conn = mysql_db()
    alarms = db_get_alarms_list(conn, alarms_kind)
    alarms_list = []
    for alarm in alarms:
        is_updated_today = is_today(alarm[15])
        start_date_obj = datetime.strptime(alarm[1], '%Y-%m-%d').date()
        end_date_obj = datetime.strptime(alarm[2], '%Y-%m-%d').date()
        # 判断当前日期是否在开始日期和结束日期范围内
        if start_date_obj <= now.date() <= end_date_obj:
            if alarm[4] == '每天' or alarm[4] == weekday or alarm[4] == date_str:
                if alarm[12] == 0 and alarm[13] == 0:
                    # 将每个闹钟的 ID 添加到列表中
                    alarms_list.append({
                        "id": alarm[0],
                        "start_date": alarm[1],
                        "end_date": alarm[2],
                        "alarm_time": alarm[3],
                        "frequency": alarm[4],
                        "alarm_title": alarm[5],
                        "alarm_detail": alarm[6],
                        "alarm_link": alarm[7],
                        "alarm_kind": alarm[8],
                        "is_done_today": alarm[9],
                        "week_already_count": alarm[10],
                        "month_already_count": alarm[11],
                        "week_count": alarm[12],
                        "month_count": alarm[13]
                    })
                    # 假如周次数不限制，月次数限制，只需要比较月次数即可
                if alarm[12] == 0 and alarm[13] > 0:
                    # 如果是今天是做了最后一次，展示
                    if alarm[11] == alarm[13] and not is_updated_today:
                        continue
                    else:
                        # 将每个闹钟的 ID 添加到列表中
                        alarms_list.append({
                            "id": alarm[0],
                            "start_date": alarm[1],
                            "end_date": alarm[2],
                            "alarm_time": alarm[3],
                            "frequency": alarm[4],
                            "alarm_title": alarm[5],
                            "alarm_detail": alarm[6],
                            "alarm_link": alarm[7],
                            "alarm_kind": alarm[8],
                            "is_done_today": alarm[9],
                            "week_already_count": alarm[10],
                            "month_already_count": alarm[11],
                            "week_count": alarm[12],
                            "month_count": alarm[13]
                        })
                    # 周次数限制，月次数限制
                if alarm[12] > 0 and alarm[13] > 0:
                    print("is_updated_today=", is_updated_today)
                    # 周次数限制，本周次数已达到最大限制
                    if alarm[10] >= alarm[12] and not is_updated_today:
                        continue
                    # 本周已做次数未超过周次数最大限制，但是月次数已超过最大限制
                    if alarm[11] >= alarm[13] and not is_updated_today:
                        continue
                    alarms_list.append({
                        "id": alarm[0],
                        "start_date": alarm[1],
                        "end_date": alarm[2],
                        "alarm_time": alarm[3],
                        "frequency": alarm[4],
                        "alarm_title": alarm[5],
                        "alarm_detail": alarm[6],
                        "alarm_link": alarm[7],
                        "alarm_kind": alarm[8],
                        "is_done_today": alarm[9],
                        "week_already_count": alarm[10],
                        "month_already_count": alarm[11],
                        "week_count": alarm[12],
                        "month_count": alarm[13]
                    })
    # 将alarms_list转换为字典
    alarms_dict = {"list": alarms_list}
    # 返回JSON数据
    return alarms_dict


# 闹钟创建
def create_alarm():
    # 连接到数据库
    conn = mysql_db()
    cursor = conn.cursor()
    # 从请求中获取数据
    data = request.get_json()
    start_date = data.get('start_date')
    end_date = data.get('end_date')
    final_time = data.get('alarm_time')
    alarm_frequency = data.get('frequency')
    alarm_title = data.get('alarm_title')
    alarm_detail = data.get('alarm_detail')
    alarm_link = data.get('alarm_link')
    alarm_kind = data.get('alarm_kind')
    week_count = data.get('week_count')
    month_count = data.get('month_count')
    # 定义SQL语句
    sql = '''
        INSERT INTO alarms(start_date, end_date, alarm_time, frequency, alarm_title, alarm_detail, alarm_link, alarm_kind, week_count, month_count)
        VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    '''
    # 定义要插入的数据
    alarm = (
        start_date, end_date, final_time, alarm_frequency, alarm_title, alarm_detail, alarm_link, alarm_kind,
        week_count, month_count)
    # 执行SQL语句
    cursor.execute(sql, alarm)
    # 提交事务
    conn.commit()
    # 关闭游标和连接
    cursor.close()
    conn.close()

    return {'id': cursor.lastrowid}, 201


def update_alarm_detail(data):
    # 连接到数据库
    conn = mysql_db()
    cursor = conn.cursor()
    # 执行更新
    for item in data:
        week_already_count = 0
        month_already_count = 0
        if int(item['week_count']) == 0:
            month_already_count = int(item['month_already_count']) + 1
        if int(item['week_count']) > 0:
            week_already_count = int(item['week_already_count']) + 1
            month_already_count = int(item['month_already_count']) + 1
        cursor.execute("""
            UPDATE alarms
            SET 
            is_done_today = 1, 
            week_already_count = %s, 
            month_already_count = %s,
            updated_at = NOW()
            WHERE id = %s
        """, (week_already_count, month_already_count, item['id'],))
    # 提交事务
    conn.commit()
    # 关闭游标和连接
    cursor.close()
    conn.close()
    # 返回成功信息
    return {'status': 'success'}


def update_alarm_card_detail(data):
    # 连接到数据库
    conn = mysql_db()
    cursor = conn.cursor()
    # 执行更新
    for item in data:
        if item['week_count'] == 0 or item['month_count'] == 0:
            cursor.execute("""
                UPDATE alarms
                SET 
                is_done_today = 1, 
                updated_at = NOW()
                WHERE id = %s
            """, (item['id'],))
        else:
            cursor.execute("""
                UPDATE alarms
                SET 
                is_done_today = 1, 
                week_already_count = week_already_count + 1, 
                month_already_count = month_already_count + 1,
                updated_at = NOW()
                WHERE id = %s
            """, (item['id'],))
    # 提交事务
    conn.commit()
    # 关闭游标和连接
    cursor.close()
    conn.close()
    # 返回成功信息
    return {'status': 'success'}


def alarms_status_reset(id):
    # 获取今天的日期和星期
    today = datetime.now()

    # 连接到数据库
    conn = mysql_db()
    cursor = conn.cursor()
    # 执行更新
    if today.day == 1:
        # 如果今天是每月的第一天，重置 month_already_count 和 week_already_count
        cursor.execute("""
            UPDATE alarms
            SET 
            month_already_count = 0,
            week_already_count = 0,
            is_done_today = 0
            WHERE id = %s
        """, (id,))
    elif today.weekday() == 0:
        # 如果今天是每周的第一天（周一），重置 week_already_count
        cursor.execute("""
            UPDATE alarms
            SET 
            week_already_count = 0,
            is_done_today = 0
            WHERE id = %s
        """, (id,))
    else:
        # 否则，只重置 is_done_today
        cursor.execute("""
            UPDATE alarms
            SET 
            is_done_today = 0
            WHERE id = %s
        """, (id,))
    # 提交事务
    conn.commit()
    # 关闭游标和连接
    cursor.close()
    conn.close()


# 删除闹钟列表中已经过期或者错误的闹钟
def handle_alarm_item_delete(data):
    # 连接到数据库
    conn = mysql_db()
    cursor = conn.cursor()
    # 执行更新
    for item in data:
        cursor.execute("""
                DELETE FROM alarms
                WHERE id = %s
            """, (item['id'],))
    # 提交事务
    conn.commit()
    # 关闭游标和连接
    cursor.close()
    conn.close()
    # 返回成功信息
    return {'status': 'success'}
